package dbadapters;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import entities.CoSigner;
import entities.FinancialInfo;
import entities.Loan;
import entities.Student;

/**
 * used for adding new students to the db
 * 
 * @author gke703
 */
public class AddStudentDB {

	/**
	 * adds a student and affiliated info to the db
	 * 
	 * @param mystudent
	 *            student to be added to db
	 * @precond all info for mystudent is filled in (finances, cosigner, loan,
	 *          etc.)
	 * @precond student has not already been placed in db
	 */
	public AddStudentDB(Student mystudent) {
		try {
			Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException cnfe) {
			System.out.println("Couldn't find driver class:");
			cnfe.printStackTrace();
		}

		Connection c = null;
		try {
			c = DriverManager.getConnection(
					"jdbc:postgresql://papyrus.usask.ca/cmpt370group00",
					"cmpt370group00", "an7x482d");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println(e);
		}

		Statement stmt = null;
		try {
			stmt = c.createStatement();
		} catch (SQLException e3) {
			// TODO Auto-generated catch block
			e3.printStackTrace();
		}

		// build the query used to insert the student and related information
		String studentquery = "INSERT INTO students VALUES (";
		studentquery += mystudent.getID() + ", ";
		studentquery += "'" + mystudent.getPassword() + "', ";
		studentquery += "'" + mystudent.getName() + "', ";
		studentquery += "'" + mystudent.getBirthdateAsString() + "', ";
		studentquery += "'" + mystudent.getAddress() + "', ";
		studentquery += "'" + mystudent.getPhoneNumber() + "', ";
		studentquery += "'" + mystudent.getEmail() + "', ";
		studentquery += "'" + mystudent.getInstitution() + "', ";
		studentquery += "'" + mystudent.getIsCitizen() + "', ";
		studentquery += "'" + mystudent.getMaritalStatus() + "', ";
		studentquery += "'" + mystudent.getFatherEducation() + "', ";
		studentquery += "'" + mystudent.getMotherEducation() + "', ";
		studentquery += "'" + mystudent.getSIN() + "', ";
		studentquery += "'" + mystudent.getPaymentPlanCreated() + "')";

		// creat queary used to insert payment_plan
		String planquery = "";
		if (mystudent.getPaymentPlanCreated()) {
			planquery = "INSERT INTO payment_plan VALUES (";
			planquery += mystudent.getID() + ", ";
			planquery += mystudent.getPaymentPlan().getLoanAmount() + ", ";
			planquery += mystudent.getPaymentPlan().getMonthlyPayment() + ", ";
			planquery += mystudent.getPaymentPlan().getAmountReceived() + ", ";
			planquery += mystudent.getPaymentPlan().getNextAmount() + ", ";
			planquery += mystudent.getPaymentPlan().getPrevAmount() + ", ";
			planquery += "'" + mystudent.getPaymentPlan().getNextDateAsString()
					+ "', ";
			planquery += "'" + mystudent.getPaymentPlan().getPrevDateAsString()
					+ "')";
		}

		// create query used to insert student financial info
		String finquery = "INSERT INTO students_financialinfo VALUES (";
		finquery += mystudent.getID() + ", ";
		finquery += mystudent.getFinances().getPersonalIncome() + ", ";
		finquery += mystudent.getFinances().getHouseholdIncome() + ", ";
		finquery += mystudent.getFinances().getTaxableEarnings() + ", ";
		finquery += mystudent.getFinances().getChildSupport() + ", ";
		finquery += mystudent.getFinances().getScholarship() + ", ";
		finquery += mystudent.getFinances().getMonthlyExpenses() + ")";

		// create query used to insert student loan info
		String loanquery = "INSERT INTO students_loans VALUES (";
		loanquery += mystudent.getID() + ", ";
		loanquery += mystudent.getLoan().getAmountRequested() + ", ";
		loanquery += mystudent.getLoan().getAmountGiven() + ", ";
		loanquery += "'" + mystudent.getLoan().isNew() + "', ";
		loanquery += "'" + mystudent.getLoan().isApproved() + "')";

		CoSigner mycosign = mystudent.getLoan().getCosigner();

		// create query used to insert cosigner info
		String cosignquery = "INSERT INTO cosigners VALUES (";
		cosignquery += mycosign.getID() + ", ";
		cosignquery += mystudent.getID() + ", ";
		cosignquery += "'" + mycosign.getName() + "', ";
		cosignquery += "'" + mycosign.getBirthdateAsString() + "', ";
		cosignquery += "'" + mycosign.getAddress() + "', ";
		cosignquery += "'" + mycosign.getPhoneNumber() + "', ";
		cosignquery += "'" + mycosign.getEmail() + "', ";
		cosignquery += mycosign.getFinancialInfo().getPersonalIncome() + ", ";
		cosignquery += mycosign.getFinancialInfo().getHouseholdIncome() + ", ";
		cosignquery += mycosign.getFinancialInfo().getTaxableEarnings() + ", ";
		cosignquery += mycosign.getFinancialInfo().getChildSupport() + ", ";
		cosignquery += mycosign.getFinancialInfo().getScholarship() + ", ";
		cosignquery += mycosign.getFinancialInfo().getMonthlyExpenses() + ")";

		// System.out.println(studentquery);
		// System.out.println(finquery);
		// System.out.println(loanquery);
		// System.out.println(cosignquery);

		// check if student is in db already
		boolean isValid = true;
		ResultSet rs = null;
		try {
			rs = stmt.executeQuery("SELECT * FROM students WHERE student_id="
					+ mystudent.getID());
		} catch (SQLException e3) {
			// TODO Auto-generated catch block
			e3.printStackTrace();
		}
		try {
			if (rs.next()) {
				isValid = false;
			}
		} catch (SQLException e3) {
			// TODO Auto-generated catch block
			e3.printStackTrace();
		}

		// Cleanup
		try {
			rs.close();
		} catch (SQLException e2) {
			// TODO Auto-generated catch block
			e2.printStackTrace();
		}

		if (isValid) {
			try {
				stmt.execute(studentquery);
				stmt.execute(finquery);
				stmt.execute(loanquery);
				stmt.execute(cosignquery);
				if (mystudent.getPaymentPlanCreated()) {
					stmt.execute(planquery);
				}
			} catch (SQLException e3) {
				// TODO Auto-generated catch block
				e3.printStackTrace();
			}
		}

		try {
			stmt.close();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			c.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * regression test for the adapter Since test modifies database, it should
	 * not be called without inspection of database
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		Student mystudent = new Student(123456, "123456");
		mystudent.setAddress("123 Fake St");
		mystudent.setBirthdate("01/01/2005");
		mystudent.setCitizenship(true);
		mystudent.setEmail("123@google.com");
		mystudent.setFatherEducation(Student.education.DEGREE);
		mystudent.setInstitution("UofS");
		mystudent.setName("Stewie Griffin");
		mystudent.setSIN("465467823");
		mystudent.setPhoneNumber("222-2222");
		mystudent.setMotherEducation(Student.education.HIGH_SCHOOL);
		mystudent.setMaritalStatus(Student.maritalStatuses.SINGLE);

		Loan loan1 = new Loan(mystudent);
		loan1.setAmountRequested(5000.00);
		loan1.setIsNew(true);
		mystudent.setLoan(loan1);

		FinancialInfo finInfo1 = new FinancialInfo();
		finInfo1.setChildSupport(0);
		finInfo1.setHouseholdIncome(100000);
		finInfo1.setPersonalIncome(10000);
		finInfo1.setScholarship(0);
		finInfo1.setTaxableEarnings(0);
		mystudent.setFinances(finInfo1);

		CoSigner cosigner1 = new CoSigner(789789);
		cosigner1.setName("Cleveland");
		loan1.setCosigner(cosigner1);
		cosigner1.setAddress("123 Fake St");
		cosigner1.setBirthdate("01/01/2005");
		cosigner1.setCosignedLoan(loan1);
		cosigner1.setEmail("123@google.com");
		cosigner1.setPhoneNumber("789-7979");

		FinancialInfo myF = new FinancialInfo();

		myF.setChildSupport(0);
		myF.setHouseholdIncome(100000);
		myF.setPersonalIncome(10000);
		myF.setScholarship(0);
		myF.setTaxableEarnings(0);
		cosigner1.setFinancialInfo(myF);

		new AddStudentDB(mystudent);
	}
}
